
package modelo.dao.impl;

import java.sql.*;
import java.util.*;

import modelo.dao.inter.ITelefoneDAO;
import modelo.dto.*;

public class TelefoneImpl extends GenericoImpl implements ITelefoneDAO {

	public TelefoneImpl(Connection con) {
		super(con);
	}

    @Override
    public boolean cadastrar(Object obj) throws SQLException {
    	Telefone telefone = (Telefone) obj;
        
        super.sql = "INSERT INTO telefone(numero, fk_operadora, fk_contato) VALUES(?, ?, ?)";
        
        try {
            super.pstm = super.conn.prepareStatement(super.sql);
            super.pstm.setString(1, telefone.getNumero());
            super.pstm.setObject(2, telefone.getOperadora().getCodigo());
            super.pstm.setInt(3, telefone.getContato().getCodigo());
            
            super.efetuou = !super.pstm.execute();
            
            super.conn.commit();
                                   
        } 
        catch (SQLException e) {
        	super.conn.rollback();
        	e.printStackTrace();			
            System.out.println("Erro: "+e.getMessage());
            throw new SQLException(e.getMessage());
        }
        
        return super.efetuou;
    }

    @Override
    public boolean atualizar(Object obj) throws SQLException {
    	Telefone telefone = (Telefone) obj;
        
        super.sql = "UPDATE telefone SET numero= ?, fk_operadora= ?, fk_contato= ?  WHERE id_telefone= ?";
        
        try {
            super.pstm = super.conn.prepareStatement(super.sql);
            super.pstm.setString(1, telefone.getNumero());
            super.pstm.setObject(2, telefone.getOperadora().getCodigo());
            super.pstm.setInt(3, telefone.getContato().getCodigo());
            super.pstm.setInt(4, telefone.getCodigo());
            
            System.out.println(super.pstm.toString());
            
            super.efetuou = !super.pstm.execute();
            
            super.conn.commit();
        } 
        catch (SQLException e) {
        	super.conn.rollback();
        	e.printStackTrace();
            System.out.println("Erro: "+e.getMessage());
            throw new SQLException(e.getMessage());
        }
        
        return super.efetuou;
    }

    @Override
    public boolean excluir(Object obj) throws SQLException {
    	Telefone telefone = (Telefone) obj;
        
        super.sql = "DELETE FROM telefone WHERE id_telefone= ?";
        
        try {                        
            super.pstm = super.conn.prepareStatement(super.sql);
            super.pstm.setInt(1, telefone.getCodigo());
            
            super.efetuou = !super.pstm.execute();
            
            super.conn.commit();
        } 
        catch (SQLException e) {
        	super.conn.rollback();
        	e.printStackTrace();
            System.out.println("Erro: "+e.getMessage());
            throw new SQLException(e.getMessage());
        }
        
        return super.efetuou;
    }

    @Override
    public List<Telefone> buscarTodos() throws SQLException {
    	List<Telefone> tels = new ArrayList<Telefone>();        
        Telefone telefone = null;
        
        super.sql = "SELECT * FROM telefone ORDER BY numero";
        
        try {                        
            super.pstm = super.conn.prepareStatement(super.sql);            
            super.res = super.pstm.executeQuery();
            
            super.conn.commit();
            
            while(super.res.next()) {
            	telefone = new Telefone();
                telefone.setCodigo(super.res.getInt("id_telefone"));
                telefone.setNumero(super.res.getString("numero"));

                telefone.setOperadora(new Operadora(super.res.getInt("id_operadora")));
                telefone.setContato(new Contato(super.res.getInt("id_contato")));
                
                tels.add(telefone);                                                                            
            }
                        
        } 
        catch (SQLException e) {
        	super.conn.rollback();
        	e.printStackTrace();        	        	
            System.out.println("Erro: "+e.getMessage());
            throw new SQLException(e.getMessage());
        }
        
        return tels;
    }

    @Override
    public Telefone getObjetoById(Object obj) throws SQLException {
    	Telefone telefone = (Telefone) obj;
        
        super.sql = "SELECT * FROM telefone WHERE id_telefone= ? ORDER BY numero";
        
        try {                        
            super.pstm = super.conn.prepareStatement(super.sql);            
            super.pstm.setInt(1, telefone.getCodigo());
            
            super.res = super.pstm.executeQuery();
            
            super.conn.commit();
            
            if(super.res.next()){            	            	            
                telefone = new Telefone(super.res.getInt("id_telefone"), super.res.getString("numero"));
                telefone.setContato(new Contato(super.res.getInt("fk_contato")));
                telefone.setOperadora(new Operadora(super.res.getInt("fk_operadora")));
            }
            
        } 
        catch (SQLException e) {        	
        	super.conn.rollback();
            System.out.println("Erro: "+e.getMessage());
            throw new SQLException(e.getMessage());
        }
        
        return telefone;
    }

	@Override
	public List<Telefone> buscarTelefoneAndOperadora(Contato contato) throws SQLException {
		List<Telefone> tels = new ArrayList<Telefone>();
		Telefone tel = null;
		Operadora oper = null;
		
		super.sql = "SELECT * FROM telefone_operadora as t WHERE t.fk_contato = ?";
		
		try {
			super.pstm = conn.prepareStatement(super.sql);
			super.pstm.setInt(1, contato.getCodigo());
			super.res = super.pstm.executeQuery();
			
			super.pstm.getConnection().commit();
				
			while(super.res.next()) {
				tel = new Telefone(super.res.getInt("id_telefone"), super.res.getString("numero"));
				
				oper = new Operadora();
				oper.setNomeOperadora(super.res.getString("descricao"));
				
				tel.setOperadora(oper);
				tels.add(tel);
			}
			
		}
		catch (SQLException e) {
			super.pstm.getConnection().rollback();
			e.printStackTrace();
			System.out.println("Erro: "+e.getMessage());
			throw new SQLException(e.getMessage());
		}
				
		return tels;
	}

	@Override
	public List<Telefone> pesquisaAvancada(Contato contato, Operadora operadora) throws SQLException {
		List<Telefone> tels = new ArrayList<Telefone>();
		
		super.sql = "SELECT * FROM telefone_operadora WHERE fk_contato= ? OR fk_operadora= ?";
		
		try {
			super.pstm = conn.prepareStatement(super.sql);
			super.pstm.setInt(1, contato.getCodigo());
			super.pstm.setInt(2, operadora.getCodigo());
			super.res = super.pstm.executeQuery();
			
			super.pstm.getConnection().commit();
				
			while(super.res.next()) {
				Telefone tel = new Telefone(super.res.getInt("id_telefone"), super.res.getString("numero"));
				tel.getOperadora().setNomeOperadora(super.res.getString("descricao"));				
				
				tels.add(tel);
			}
			
		}
		catch (SQLException e) {
			super.pstm.getConnection().rollback();
			e.printStackTrace();
			System.out.println("Erro: "+e.getMessage());
			throw new SQLException(e.getMessage());
		}
				
		return tels;
	}

}
